package com.yss.reportworld.service.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.*;

import com.alibaba.druid.stat.TableStat;
import com.alibaba.fastjson.JSONArray;
import com.baomidou.mybatisplus.annotation.DbType;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.yss.common.core.constant.RequestDataHelper;
import com.yss.common.core.domain.MindTreeNode;
import com.yss.common.core.enums.YssStatusEnum;
import com.yss.common.core.exception.BusinessException;
import com.yss.common.core.utils.JdbcUtil;
import com.yss.common.core.utils.file.FileUtils;
import com.yss.common.core.utils.sql.SqlUtil;
import com.yss.common.core.utils.uuid.IdUtils;
import com.yss.common.security.utils.DictUtils;
import com.yss.common.security.utils.SecurityUtils;
import com.yss.reportworld.domain.Table;
import com.yss.reportworld.enums.ReportWorldEnum;
import com.yss.reportworld.mapper.TableMapper;
import com.yss.reportworld.util.TableInfoUtil;
import com.yss.system.api.domain.SysDictData;
import lombok.extern.slf4j.Slf4j;
import com.yss.common.core.utils.StringUtils;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.stereotype.Service;
import com.yss.reportworld.service.IDataMapService;
import com.yss.reportworld.mapper.DataMapMapper;
import com.yss.reportworld.domain.DataMap;
import org.springframework.web.multipart.MultipartFile;

import javax.sql.DataSource;

/**
 * 报表世界数据地图Service业务层处理
 *
 * @author yss
 * @date 2023-05-25
 */
@Slf4j
@Service
public class DataMapServiceImpl extends ServiceImpl<DataMapMapper, DataMap> implements IDataMapService {
    @Autowired
    private TableMapper tableMapper;
    @Qualifier("dataSource")
    @Autowired
    private DataSource dataSource;

    /**
     * 查询报表世界数据地图列表
     *
     * @param dataMap 报表世界数据地图
     * @return 报表世界数据地图
     */
    @Override
    public List<DataMap> selectDataMapList(DataMap dataMap) {
        try {
            TableInfoUtil.handleTableVersion(dataMap.getHisVersion()); // 替换表名
            QueryWrapper<DataMap> wrapper = new QueryWrapper();
            wrapper.orderByAsc("TABLE_NAME,TABLE_NAME_RELY");
            if (StringUtils.isNull(dataMap)) {
                return this.list(wrapper);
            }
            if (StringUtils.isNotEmpty(dataMap.getTableName())) {
                wrapper.like("TABLE_NAME", dataMap.getTableName());
            }
            if (StringUtils.isNotEmpty(dataMap.getTableNameRely())) {
                wrapper.like("TABLE_NAME_RELY", dataMap.getTableNameRely());
            }
            if (StringUtils.isNotEmpty(dataMap.getColumnName())) {
                wrapper.like("COLUMN_NAME", dataMap.getColumnName());
            }
            if (StringUtils.isNotEmpty(dataMap.getColumnNameRely())) {
                wrapper.like("COLUMN_NAME_RELY", dataMap.getColumnNameRely());
            }
            if (StringUtils.isNotEmpty(dataMap.getFileName())) {
                wrapper.like("FILE_NAME", dataMap.getFileName());
            }
            if (StringUtils.isNotEmpty(dataMap.getVersion())) {
                wrapper.like("VERSION", dataMap.getVersion());
            }
            if (null != dataMap.getStatus()) {
                wrapper.like("STATUS", dataMap.getStatus());
            }
            // 导出的时候选择了数据，按照选择的数据
            if (StringUtils.isNotEmpty(dataMap.getIds())) {
                List<String> list = JSONArray.parseArray(dataMap.getIds(), String.class);
                if (StringUtils.isNotEmpty(list)) {
                    wrapper.in("ID", list);
                }
            }
            // this.getDatamapInfoBySql();
            return this.list(wrapper);
        } finally {
            RequestDataHelper.removeThreadLocal();
        }
    }

    /**
     * 校验是否已存在数据
     *
     * @param dataMap
     * @return 结果
     */
    @Override
    public boolean checkDataMapUnique(DataMap dataMap) {
        String dataMapId = StringUtils.isNull(dataMap.getId()) ? "-1" : dataMap.getId();
        QueryWrapper<DataMap> wrapper = new QueryWrapper<>();
        wrapper.eq("TABLE_NAME", dataMap.getTableName());
        wrapper.eq("TABLE_NAME_RELY", dataMap.getTableNameRely());
        if (StringUtils.isNotEmpty(dataMap.getColumnName())) {
            wrapper.eq("COLUMN_NAME", dataMap.getColumnName());
        }
        if (StringUtils.isNotEmpty(dataMap.getColumnNameRely())) {
            wrapper.eq("COLUMN_NAME_RELY", dataMap.getColumnNameRely());
        }
        DataMap info = this.getOne(wrapper);
        if (StringUtils.isNotNull(info) && !info.getId().equals(dataMapId)) {
            return false;
        }
        return true;
    }

    /**
     * 递归获取一个表依赖的所有表信息
     *
     * @param tableName
     * @return
     */
    public List<MindTreeNode> querMindTreeNode(String tableName) {
        List<MindTreeNode> listMindTreeNode = new ArrayList<>();
        MindTreeNode treeNode = new MindTreeNode();
        treeNode.setId(tableName);
        treeNode.setTopic(tableName);
        treeNode.setIsroot(true);
        listMindTreeNode.add(treeNode);
        List<String> idList = new ArrayList<>();
        recursionTree(tableName, listMindTreeNode, idList);
        Map<String, SysDictData> sysDictDataMap = DictUtils.getDictDataCacheMap(ReportWorldEnum.REPORT_TABLE_LAYERED.getCode());
        for (int i = 0; i < listMindTreeNode.size(); i++) {
            MindTreeNode node = listMindTreeNode.get(i);
            if (StringUtils.isNull(node) || StringUtils.isEmpty(node.getId())) {
                continue;
            }
            QueryWrapper<Table> wrapper = new QueryWrapper<>();
            wrapper.eq("NAME", node.getId());
            Table table = tableMapper.selectOne(wrapper);
            node.setId(idList.get(i));// 替换对应数据的ID,页面上重复的id会被过滤
            if (StringUtils.isNull(table) || StringUtils.isEmpty(table.getName())) {
                //不在系统的就是外部的表
                Map<String, Object> map = new HashMap<>();
                map.put("background-color", "red");
                map.put("font-size", 12);
                // map.put("width",100);
                node.setData(map);
                continue;
            }
            Map<String, Object> map = new HashMap<>();
            if (null == table.getLayered()) {
                map.put("background-color", "#6E3B80");
            } else {
                if (StringUtils.isNotEmpty(sysDictDataMap) && sysDictDataMap.containsKey(table.getLayered().toString())) {
                    map.put("background-color", sysDictDataMap.get(table.getLayered().toString()).getCssClass());
                }
            }
            map.put("font-size", 12);
            node.setTableName(node.getTopic());
            // map.put("width",500);
            node.setData(map);
            node.setTopic(node.getTopic() + "【" + table.getChineseName() + "】");
        }
        return listMindTreeNode;
    }


    private void recursionTree(String tableName, List<MindTreeNode> listMindTreeNode, List<String> idList) {
        QueryWrapper<DataMap> wrapper = new QueryWrapper();
        wrapper.select("TABLE_NAME,TABLE_NAME_RELY");
        wrapper.eq("STATUS", YssStatusEnum.NORMAL.getCode());
        wrapper.eq("TABLE_NAME", tableName);
        wrapper.groupBy("TABLE_NAME,TABLE_NAME_RELY");
        List<DataMap> list = this.list(wrapper);
        // 每次查询的数据，对应的parentid替换成随机ID
        String id = IdUtils.fastSimpleUUID();
        idList.add(id);
        if (StringUtils.isEmpty(list)) {
            return;
        }
        for (DataMap dataMap : list) {
            listMindTreeNode.add(this.handDataMapToNode(dataMap, id));
            recursionTree(dataMap.getTableNameRely(), listMindTreeNode, idList);
        }

    }

    /**
     * 转换成前台要的数结构
     */
    private MindTreeNode handDataMapToNode(DataMap dataMap, String id) {
        MindTreeNode node = new MindTreeNode();
        node.setId(dataMap.getTableNameRely());
        node.setParentid(id);
        node.setTopic(dataMap.getTableNameRely());
        return node;
    }

    @Override
    public String readDatamapInfo(MultipartFile multipartfile, boolean updateSupport) {
        StringBuffer buffer = new StringBuffer();
        try {
            if (null == multipartfile) {
                throw new BusinessException("没有获取到文件，请稍后重试！");
            }
            List<List<Object>> listData = FileUtils.readerExcelObj(FileUtils.MultipartFileToFile(multipartfile));
            // 获取现在最大的版本号
            List<SysDictData> dictDatas = DictUtils.getDictCache(ReportWorldEnum.REPORT_VERSION.getCode());
            if (StringUtils.isEmpty(dictDatas)) {
                throw new BusinessException("没有获取到版本号，请稍后重试！");
            }
            String version = dictDatas.get(0).getDictValue();
            if (StringUtils.isBlank(version)) {
                throw new BusinessException("没有获取到版本号，请稍后重试！");
            }
            if (StringUtils.isEmpty(listData) || listData.size() == 1) {
                throw new BusinessException("没有获取到文件的内容，请稍后重试！");
            }
            List<DataMap> list = new ArrayList<>();
            for (int i = 1; i < listData.size(); i++) {
                List<Object> objectList = listData.get(i);
                if (StringUtils.isEmpty(objectList)) {
                    continue;
                }
                Object tableNameRely = objectList.get(0); // 被依赖表名
                Object tableName = objectList.get(1);// 表名
                Object columnNameRely = objectList.get(2);// 依赖字段
                Object columnName = objectList.get(3);// 字段名称
                Object remark = objectList.get(4);// 备注
                if (null == tableNameRely) {
                    throw new BusinessException("没有获取到被依赖表名，请补充完整！");
                }
                if (null == tableName) {
                    throw new BusinessException("没有获取到表名信息，请补充完整！");
                }
                // 查询是不是已经存在
                QueryWrapper<DataMap> wrapper = new QueryWrapper<>();
                wrapper.eq("TABLE_NAME", tableName);
                /*QueryWrapper<Table> tableQueryWrapper = new QueryWrapper<>();
                tableQueryWrapper.eq("NAME", tableName);
                Table table = tableMapper.selectOne(tableQueryWrapper);
                if (StringUtils.isNull(table)) {
                    throw new BusinessException("系统没有获取到表【" + tableName + "】信息，请检查！");
                }*/
                wrapper.eq("TABLE_NAME_RELY", tableNameRely);
              /*  QueryWrapper<Table> tableRelyQueryWrapper = new QueryWrapper<>();
                tableRelyQueryWrapper.eq("NAME", tableNameRely);
                Table tableRely = tableMapper.selectOne(tableRelyQueryWrapper);
                if (StringUtils.isNull(tableRely)) {
                    throw new BusinessException("系统没有获取到依赖表【" + tableNameRely + "】信息，请检查！");
                }*/
                if (null != columnName && StringUtils.isNotEmpty(columnName.toString())) {
                   /* QueryWrapper<TableDetail> tableDetailQueryWrapper = new QueryWrapper<>();
                    tableDetailQueryWrapper.eq("TABLE_ID", table.getId());
                    tableDetailQueryWrapper.eq("COLUMN_NAME", columnName);
                    TableDetail tableDetail = tableDetailService.getOne(tableDetailQueryWrapper);
                    if (StringUtils.isNull(tableDetail)) {
                        throw new BusinessException("系统没有获取到表【" + tableName + "】的字段【" + columnName + "】信息，请检查！");
                    }*/
                    wrapper.eq("COLUMN_NAME", columnName.toString());
                }
                if (null != columnNameRely && StringUtils.isNotEmpty(columnNameRely.toString())) {
             /*       QueryWrapper<TableDetail> tableRelyDetailQueryWrapper = new QueryWrapper<>();
                    tableRelyDetailQueryWrapper.eq("TABLE_ID", tableRely.getId());
                    tableRelyDetailQueryWrapper.eq("COLUMN_NAME", columnNameRely);
                    TableDetail tableDetailRely = tableDetailService.getOne(tableRelyDetailQueryWrapper);
                    if (StringUtils.isNull(tableDetailRely)) {
                        throw new BusinessException("系统没有获取到表【" + tableNameRely + "】的字段【" + columnNameRely + "】信息，请检查！");
                    }*/
                    wrapper.eq("COLUMN_NAME_RELY", columnNameRely.toString());
                }
                this.remove(wrapper);
           /*     if (StringUtils.isNotNull(info)) {
                    buffer.append("数据已经存在").append("表【" + tableName + "】").append("被依赖表【" + tableNameRely + "】")
                            .append((null != columnName && StringUtils.isNotEmpty(columnName.toString())) ? "字段名称【" + columnName + "】" : "")
                            .append((null != columnNameRely && StringUtils.isNotEmpty(columnNameRely.toString())) ? "依赖字段名称【" + columnNameRely + "】" : "")
                            .append("系统已经存在！")
                            .append("\r\n");
                    continue;
                }*/
                DataMap dataMap = new DataMap();
                dataMap.setId(IdUtils.fastSimpleUUID());
                dataMap.setTableNameRely(tableNameRely.toString());
                dataMap.setTableName(tableName.toString());
                if (null != columnNameRely && StringUtils.isNotEmpty(columnNameRely.toString())) {
                    dataMap.setColumnNameRely(columnNameRely.toString());
                }
                if (null != columnName && StringUtils.isNotEmpty(columnName.toString())) {
                    dataMap.setColumnName(columnName.toString());
                }
                dataMap.setCreateBy(SecurityUtils.getUsername());
                dataMap.setCreateTime(new Date());
                dataMap.setStatus(YssStatusEnum.NORMAL.getCode());
                dataMap.setVersion(version);
                if (StringUtils.isNotNull(remark)) {
                    dataMap.setRemark(remark.toString());
                }
                list.add(dataMap);
            }
            if (!StringUtils.isEmpty(list)) {
                this.saveBatch(list);
            }
            buffer.append("成功读取").append("数" + (list.size()) + "条").append("\r\n");
        } catch (Exception ex) {
            log.error(ex.getMessage());
            throw new BusinessException(ex.getMessage());
        }
        return buffer.toString();
    }


    /**
     * 根据结果集和ETL采集获取表关联关系
     *
     * @return
     */
    public String getDatamapInfoBySql(String hisVersion) {
        StringBuffer buffer = new StringBuffer();
        ResultSet rs = null;
        PreparedStatement pStemtQuery = null;
        // 获取现在最大的版本号
        List<SysDictData> dictDatas = DictUtils.getDictCache(ReportWorldEnum.REPORT_VERSION.getCode());
        if (StringUtils.isEmpty(dictDatas)) {
            throw new BusinessException("没有获取到版本号，请稍后重试！");
        }
        String version = dictDatas.get(0).getDictValue();
        if (StringUtils.isBlank(version)) {
            throw new BusinessException("没有获取到版本号，请稍后重试！");
        }
        Connection conn = null;
        String versionSuffix = TableInfoUtil.getVersionSuffix(hisVersion);
        try {
            // 获取所有存在的依赖信息
            List<DataMap> list = this.list();
            Map<String, String> mapDataMap = new HashMap<>();
            if (StringUtils.isNotEmpty(list)) {
                for (DataMap dataMap : list) {
                    mapDataMap.put(dataMap.getTableName() + "\t" + dataMap.getTableNameRely(), dataMap.getTableName());
                }
            }
         /*   DynamicRoutingDataSource ds = (DynamicRoutingDataSource) dataSource;
            Connection slave = ds.getDataSource("slave").getConnection();
            conn = slave.unwrap(OracleConnection.class);*/
            conn = dataSource.getConnection();
            if (null == conn) {
                throw new BusinessException("获取数据库连接出错，请稍后重试！");
            }
            List<DataMap> listDataMap = new ArrayList<>();
            String sql = " select c.key_name, a.dataset_name,a.report_id, b.report_name, a.dataset_sql,b.report_tablename" +
                    "    from rw_db_tc_rep_dataset" + versionSuffix + " a" +
                    "   inner join rw_db_tc_rep_para" + versionSuffix + " b on a.report_id = b.report_id" +
                    "   inner join rw_db_tp_gl_exhib_datadict" + versionSuffix + " c on b.regulation_rule = key_id" +
                    "   where class_id = 'A00002'";
            pStemtQuery = conn.prepareStatement(sql);
            rs = pStemtQuery.executeQuery();
            while (rs.next()) {
                // sql语句
                String dataset_sql = rs.getString("dataset_sql");
                // 监管规则
                String key_name = rs.getString("key_name");
                // 报表名称
                String dataset_name = rs.getString("dataset_name");
                dataset_sql = SqlUtil.replaceReportWorldSql(dataset_sql);
                if (StringUtils.isBlank(dataset_sql)) {
                    log.error("没有取到sql语句的监管规则和报表====" + key_name + dataset_name);
                    buffer.append("没有取到sql语句的监管规则和报表====【" + key_name + "】【" + dataset_name).append("【\r\n");
                    continue;
                }
                Map<TableStat.Name, TableStat> map = null;
                try {
                    map = JdbcUtil.getSqlTableInfoMap(dataset_sql, DbType.ORACLE.getDb());
                } catch (Exception ex) {
                    log.error("解析语句出错" + ex.getMessage());
                    buffer.append("解析语句出错====【" + key_name + "】【" + dataset_name).append("】\r\n");
                }
                if (StringUtils.isEmpty(map)) {
                    log.error("该语句没有解析出来表信息");
                    buffer.append("该语句没有解析出来表信息====【" + key_name + "】【" + dataset_name).append("】\r\n");
                    continue;
                }
                for (Map.Entry<TableStat.Name, TableStat> entry : map.entrySet()) {
                    String tableNameRely = entry.getKey().getName();
                    if (StringUtils.isEmpty(tableNameRely) || StringUtils.isEmpty(rs.getString("report_tablename"))) {
                        continue;
                    }
                    if (rs.getString("report_tablename").indexOf(",") > -1) {
                        String[] arr = rs.getString("report_tablename").split(",");
                        for (String name : arr) {
                            if (StringUtils.isEmpty(name)) {
                                continue;
                            }
                            DataMap dataMap = new DataMap();
                            dataMap.setId(IdUtils.fastSimpleUUID());
                            dataMap.setTableNameRely(tableNameRely.toUpperCase());
                            dataMap.setTableName(name.toUpperCase());
                            dataMap.setCreateBy(SecurityUtils.getUsername());
                            dataMap.setCreateTime(new Date());
                            dataMap.setStatus(YssStatusEnum.NORMAL.getCode());
                            dataMap.setVersion(version);
                            dataMap.setFileName(rs.getString("dataset_sql"));// 来源
                            dataMap.setRemark("结果集");
                            if (dataMap.getTableNameRely().equals(dataMap.getTableName())) {
                                continue;
                            }
                            listDataMap.add(dataMap);
                        }
                        continue;
                    }
                    DataMap dataMap = new DataMap();
                    dataMap.setId(IdUtils.fastSimpleUUID());
                    dataMap.setTableNameRely(tableNameRely.toUpperCase());
                    dataMap.setTableName(rs.getString("report_tablename").toUpperCase());
                    dataMap.setCreateBy(SecurityUtils.getUsername());
                    dataMap.setCreateTime(new Date());
                    dataMap.setStatus(YssStatusEnum.NORMAL.getCode());
                    dataMap.setVersion(version);
                    dataMap.setFileName(rs.getString("dataset_sql"));// 来源
                    dataMap.setRemark("结果集");
                    if (dataMap.getTableNameRely().equals(dataMap.getTableName())) {
                        continue;
                    }
                    listDataMap.add(dataMap);
                }
            }
            JdbcUtil.close(rs);
            JdbcUtil.close(pStemtQuery);
            sql = "select distinct SQL_STATEMENT,TARGET_RES_ENAME from rw_db_etl_job_detail" + versionSuffix + " a where a.SQL_TYPE IN (1,2)";
            pStemtQuery = conn.prepareStatement(sql);
            rs = pStemtQuery.executeQuery();
            while (rs.next()) {
                // sql语句
                String dataset_sql = rs.getString("SQL_STATEMENT");
                dataset_sql = SqlUtil.replaceReportWorldSql(dataset_sql);
                if (StringUtils.isEmpty(rs.getString("TARGET_RES_ENAME"))) {
                    continue;
                }
                Map<TableStat.Name, TableStat> map = null;
                try {
                    map = JdbcUtil.getSqlTableInfoMap(dataset_sql, DbType.ORACLE.getDb());
                } catch (Exception ex) {
                    log.error("解析语句出错" + ex.getMessage());
                    buffer.append("解析语句出错====目标资源【" + rs.getString("TARGET_RES_ENAME") + "】\r\n");
                }
                if (StringUtils.isEmpty(map)) {
                    log.error("该语句没有解析出来表信息");
                    buffer.append("该语句没有解析出来表信息====目标资源【" + rs.getString("TARGET_RES_ENAME") + "】\r\n");
                    continue;
                }
                for (Map.Entry<TableStat.Name, TableStat> entry : map.entrySet()) {
                    String tableNameRely = entry.getKey().getName();
                    if (StringUtils.isEmpty(tableNameRely) || StringUtils.isEmpty(rs.getString("TARGET_RES_ENAME"))) {
                        continue;
                    }
                    DataMap dataMap = new DataMap();
                    dataMap.setId(IdUtils.fastSimpleUUID());
                    dataMap.setTableNameRely(tableNameRely);
                    dataMap.setTableName(rs.getString("TARGET_RES_ENAME").toUpperCase());
                    dataMap.setCreateBy(SecurityUtils.getUsername());
                    dataMap.setCreateTime(new Date());
                    dataMap.setStatus(YssStatusEnum.NORMAL.getCode());
                    dataMap.setVersion(version);
                    dataMap.setFileName(rs.getString("SQL_STATEMENT"));// 来源
                    dataMap.setRemark("ETL采集");
                    if (dataMap.getTableNameRely().equals(dataMap.getTableName())) {
                        continue;
                    }
                    listDataMap.add(dataMap);
                }
            }
            if (StringUtils.isEmpty(listDataMap)) {
                buffer.append("成功读取").append("数" + (listDataMap.size()) + "条").append("\r\n");
                return buffer.toString();
            }
            List<DataMap> listDataMapNew = new ArrayList<>();
            for (DataMap dataMap : listDataMap) {
                if (mapDataMap.containsKey(dataMap.getTableName() + "\t" + dataMap.getTableNameRely())) {
                    continue;
                }
                mapDataMap.put(dataMap.getTableName() + "\t" + dataMap.getTableNameRely(), dataMap.getTableName());
                listDataMapNew.add(dataMap);
            }
            if (StringUtils.isNotEmpty(listDataMapNew)) {
                this.saveBatch(listDataMapNew);
                buffer.append("成功读取").append("数" + (listDataMapNew.size()) + "条").append("\r\n");
                return buffer.toString();
            }
        } catch (Exception ex) {
            log.error("结果集和ETL采集获取表关联关系出错：" + ex.getMessage());
        } finally {
            try {
                JdbcUtil.close(rs, pStemtQuery, conn);
            } catch (Exception ex) {
                log.error(ex.getMessage());
            }
        }
        return buffer.toString();
    }
}
